/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dnbW.dbW.sucxentdbW;

import controller.StartUp;
import dbsucxentW.OutOfMemoryException;
import dbsucxentW.SucxentShredder;
import dnbW.dbW.DatabaseEngine;
import dnbW.dbW.DatabaseInfo;
import dnbW.dbW.DataSource;
import dnbW.dbW.Document;
import dnbW.dbW.PathExpression2;
import dnbW.dbW.QueryEngine;
import dnbW.dbW.Step;
import dnbW.utilW.Stopwatch;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import javax.swing.tree.DefaultMutableTreeNode;

/**
 * 
 * @author zhouyong
 */
public class SucxentDatabaseEngine implements DatabaseEngine {

	public static final String SUXCENT_SCRIPT = StartUp.SUXCENT_SCRIPT_PATH;
	// <editor-fold defaultstate="collapsed" desc="Database">
	private DatabaseInfo contextDb;

	@Override
	public DatabaseInfo getDatabase() {
		return this.contextDb;
	}

	// </editor-fold>

	// <editor-fold desc="Database Connection">
	private Connection dbConnection;

	@Override
	public Connection getConnection() {
		return dbConnection;
	}

	// </editor-fold>

	private ResultSet lastResultSet;

	public SucxentDatabaseEngine(DatabaseInfo contextDb) {
		this.contextDb = contextDb;
	}

	@Override
	public boolean commitAdd() throws Exception {
		// build the JDBC url to connect to the master database
		String url = "jdbc:sqlserver://" + this.contextDb.getHostName()
				+ ":1433" + ";databaseName=master";

		// connect to the master database to create a new table
		Connection masterDbConnection = DriverManager.getConnection(url,
				this.contextDb.getUserName(), this.contextDb.getPassword());

		// create a new database
		Statement statement = masterDbConnection.createStatement();
		String sqlquery = "CREATE Database \"" + this.contextDb.getDbName()
				+ "\"; ";
		statement.executeUpdate(sqlquery);
		statement.close();
		masterDbConnection.close();

		this.buildConnection();

		// create an empty table to store the data source set
		statement = this.dbConnection.createStatement();
		statement
				.executeUpdate("CREATE TABLE DATASRC (\n"
						+ "NAME VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY,\n"
						+ "INFO VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS )\n");
		statement.close();

		return true;
	}

	@Override
	public void connect() throws Exception {
		this.buildConnection();
	}

	/**
	 * Builds the connection to connect to the database. Do nothing if the
	 * connection has been built.
	 * 
	 * @throws SQLException
	 */
	private void buildConnection() throws SQLException {
		if (this.dbConnection == null) {
			// build url to connect to the database
			String url = "jdbc:sqlserver://" + this.contextDb.getHostName()
					+ ":1433" + ";databaseName=" + this.contextDb.getDbName();

			// connect to the database
			try {
				dbConnection = DriverManager.getConnection(url,
						this.contextDb.getUserName(),
						this.contextDb.getPassword());
			} catch (Exception e) {
				System.out.println(this.contextDb.getDbName()
						+ ": Could not connect");
			}
		}
	}

	@Override
	public Collection<DataSource> retrieveDataSourceList() throws SQLException {
		LinkedList<DataSource> dataSources = new LinkedList<DataSource>();
		Statement statement = dbConnection.createStatement();
		ResultSet result = null;
		try {
			result = statement.executeQuery("select name from datasrc");
		} catch (Exception e) {
			statement
					.executeUpdate("CREATE TABLE DATASRC (\n"
							+ "NAME VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY,\n"
							+ "INFO VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS )\n");
			result = statement.executeQuery("select name from datasrc");
		}
		String dataSourceName;
		SucxentDataSource dataSource;
		while (result.next()) {
			dataSourceName = result.getString("name").trim();
			dataSource = new SucxentDataSource(this.contextDb, dataSourceName);
			dataSource.setLeafPathManager(new LeafPathManager(dataSource));
			dataSources.add(dataSource);
		}
		statement.close();
		return dataSources;
	}

	@Override
	public DataSource addDataSource(String dataSourceName) throws Exception {
		// Ensure our database is selected
		Statement statement = dbConnection.createStatement();
		statement.executeUpdate("USE [" + contextDb.getDbName() + "];");

		// Read and execute the script to add new data source's SUCXENT++ tables
		// to the database
		StringBuilder sqlscript = new StringBuilder();
		String line;
		BufferedReader in;
		in = new BufferedReader(new FileReader(SUXCENT_SCRIPT));
		while ((line = in.readLine()) != null) {
			line = line.replace("#SRCNAME#", dataSourceName);
			sqlscript.append(line + "\n");
		}
		in.close();
		statement.executeUpdate(sqlscript.toString());

		// Update data source name list
		statement.executeUpdate("INSERT INTO DATASRC VALUES ('"
				+ dataSourceName + "', '" + dataSourceName + "')");
		statement.close();
		SucxentDataSource dataSource = new SucxentDataSource(this.contextDb,
				dataSourceName);
		dataSource.setLeafPathManager(new LeafPathManager(dataSource));
		// dataSource.setCardinalityManager(new CardinalityManager(dataSource));
		return dataSource;
	}

	@Override
	public boolean deleteDataSource(String dataSourceName) throws Exception {
		// Ensure our database is selected
		Statement statement = dbConnection.createStatement();
		statement.executeUpdate("USE [" + contextDb.getDbName() + "];");

		// Select all tables belonging to the deleted data source and delete
		// them
		ResultSet result = statement
				.executeQuery("select name from sys.tables where name like '"
						+ dataSourceName + "%'");

		String sql = "";
		while (result.next()) {
			sql += ("DROP TABLE " + result.getString("name") + "\n");
		}
		if (!"".equals(sql.trim())) {
			statement.execute(sql);
		}

		// Update data source list
		statement.executeUpdate("DELETE FROM DATASRC WHERE NAME = '"
				+ dataSourceName + "' ");
		return true;
	}

	@Override
	public boolean delete() throws Exception {
		// Delete the bounded database from the RDBMS
		String query = "DROP Database \"" + contextDb.getDbName() + "\"; ";
		Statement statement = dbConnection.createStatement();
		statement.executeUpdate("USE [master]; ");
		statement.executeUpdate(query);
		statement.close();
		dbConnection.close();
		return true;
	}

	@Override
	public int loadDocumentFromFile(String url, String dataSourceName)
			throws Exception {
		try {
			// shred the new document
			new SucxentShredder(url, dbConnection, contextDb.getDbName(),
					dataSourceName);
			// get the data source
			SucxentDataSource dataSource = (SucxentDataSource) this.contextDb
					.getDataSource(dataSourceName);
			// update data source's document list
			this.retrieveDocuments(dataSource);

			// // update the cardinality information
			// CardinalityManager cardinalityManager =
			// dataSource.getCardinalityManager();
			Document lastDocument = dataSource.getDocument(dataSource
					.getNumDocuments());
			if (lastDocument == null)
				throw new Exception(
						"The last document has not been loaded into memory");

			// dataSource.getLeafPathManager().loadLeafPaths(); // update the
			// leaf path list
			// Collection<Document> documents = new ArrayList<Document>();
			// documents.add(lastDocument);
			// cardinalityManager.processCardinalityInformation(documents);
			return lastDocument.getDocId();
		} catch (Exception ex) {
			System.err.println(ex.getMessage());
			return -1;
		}
	}

	@Override
	public boolean deleteDocument(int docId, String dataSource)
			throws Exception {
		throw new UnsupportedOperationException(
				"Deleting Document Not supported yet.");
	}

	/**
	 * Gets the leaf values in a subtree whose root's path is the input path
	 * (possibly internal path)
	 * 
	 * @param pathExpression
	 *            Input path expression
	 * @return
	 */
	public Set<String> getLeafValues(PathExpression2 pathExpression) {
		Set<String> values = new TreeSet<String>();

		if (!pathExpression.getLastStep().isLeaf()) {
			values.add("Path Expression is not leaf! Value invalid!!!");
		} else {
			Connection dbcon = dbConnection;
			try {
				Statement statement = dbcon.createStatement();
				String query;
				List<Integer> idList = new ArrayList<Integer>();
				// Is the path an attribute path?
				if (pathExpression.getLastStep().isAttribute()) {
					// Yes. Focus on the descendant attributes only.
					this.getPathId(pathExpression, null, idList);
					query = "SELECT leafValue\n" + "FROM "
							+ pathExpression.getDataSource() + "_ATTRIBUTE\n"
							+ "WHERE PATHID IN (" + idList.get(0) + ")";
				} else {
					// No. Focus on the descendant elements only.
					this.getPathId(pathExpression, idList, null);
					query = "SELECT leafValue\n" + "FROM "
							+ pathExpression.getDataSource() + "_PATHVALUE\n"
							+ "WHERE PATHID IN (" + idList.get(0) + ")";
				}

				System.out.println("Selecting leaf value: \n" + query);
				ResultSet results = statement.executeQuery(query);
				String leafValue;
				while (results.next()) {
					leafValue = results.getString("leafvalue");
					values.add(leafValue);
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

		return values;
	}

	/**
	 * Gets the PathIDs of the descendant element paths and attribute paths of
	 * the input paht
	 * 
	 * @param pathExpression
	 *            Input path
	 * @param leafIdList
	 *            Output list of descendant element paths (null if not needed)
	 * @param attributeIdList
	 *            Output list of descendant attribute paths (null if not needed)
	 */
	public void getPathId(PathExpression2 pathExpression,
			List<Integer> leafIdList, List<Integer> attributeIdList) {
		String path = pathExpression.getDbFormatText();

		// retrieve the PathID of the descendant element paths and attribute
		// paths of the input path
		Connection dbcon = dbConnection;
		try {
			Statement statement = dbcon.createStatement();
			String query = "SELECT pathid, pathexp\n" + "FROM "
					+ pathExpression.getDataSource() + "_PATH\n"
					+ "WHERE PATHEXP LIKE '" + path + "%'";

			System.out.println("Getting ID List: \n" + query);
			ResultSet results = statement.executeQuery(query);
			int id;
			String pathExp;
			while (results.next()) {
				pathExp = results.getString("pathexp");
				id = results.getInt("pathid");

				if (pathExp.contains("@")) {
					if (attributeIdList != null) {
						attributeIdList.add(id);
					}
				} else {
					if (leafIdList != null) {
						leafIdList.add(id);
					}
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Gets the PathIDs of the descendant element paths and attribute paths of
	 * the input paht
	 * 
	 * @param path
	 *            Input path
	 * @param elementIdList
	 *            Output list of descendant element paths (null if not needed)
	 * @param attributeIdList
	 *            Output list of descendant attribute paths (null if not needed)
	 */
	public static void getPathId(dnbW.entityW.Path path,
			List<Integer> elementIdList, List<Integer> attributeIdList) {
		// retrieve the PathID of the descendant element paths and attribute
		// paths of the input path
		Connection dbcon = path.getDataSource().getDatabase().getDbConnection();
		try {
			Statement statement = dbcon.createStatement();
			String query = "SELECT pathid, pathexp\n" + "FROM "
					+ path.getDataSource().getName() + "_PATH\n"
					+ "WHERE PATHEXP LIKE '" + path.toSQLPathExp() + "%'";

			System.out.println("Getting ID List: \n" + query);
			ResultSet results = statement.executeQuery(query);
			int id;
			String pathExp;
			elementIdList.clear();
			attributeIdList.clear();
			while (results.next()) {
				pathExp = results.getString("pathexp");
				id = results.getInt("pathid");

				if (pathExp.contains("@")) {
					if (attributeIdList != null) {
						attributeIdList.add(id);
					}
				} else {
					if (elementIdList != null) {
						elementIdList.add(id);
					}
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	@Override
	public List<PathExpression2> getPaths(String dataSource) {
		try {
			// Get all paths in the data source
			Statement statement = dbConnection.createStatement();
			String sql = "SELECT P.PathExp\n" + "FROM " + dataSource.trim()
					+ "_PATH P\n";
			ResultSet resultSet = statement.executeQuery(sql);

			String path;
			String[] qNames;
			PathExpression2 pathExpression;
			Step root = null;
			Step prev = null;
			Step curr = null;

			List<PathExpression2> paths = new LinkedList<PathExpression2>();
			// paths returned from sucxent should be unique
			while (resultSet.next()) {
				// build the path expression for each path
				path = resultSet.getString(1);
				path = path.replaceAll("#", "");
				qNames = path.trim().split("\\.");

				for (String qName : qNames) {
					if ("".equals(qName.trim())) {
						continue;
					}
					curr = new Step(qName);
					if (prev == null) {
						root = curr;
					} else {
						prev.setNext(curr);
						curr.setPrev(prev);
					}
					prev = curr;
				}
				pathExpression = new PathExpression2(root, dataSource,
						getDatabase());
				paths.add(pathExpression);
				prev = null;
				root = null;
				curr = null;
			}

			return Collections.unmodifiableList(paths);
		} catch (Exception ex) {
			ex.printStackTrace();
			return Collections.EMPTY_LIST;
		}
	}

	@Override
	public QueryEngine getQueryEngine() {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	@Override
	public void execute(String queryText) throws Exception {
		ResultSet queryResultSet = null;
		Statement statement = dbConnection.createStatement(
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		try {
			// System.out.println("--- " + queryText);
			// System.out.println("Sucxent.query(Conn, queryText) executeQuery");
			queryResultSet = statement.executeQuery(queryText);
		} catch (SQLException e) {
			lastResultSet = null;
			throw e;
		} finally {
			// statement.close();
			lastResultSet = queryResultSet;
		}
	}

	@Override
	/**
	 * Build the displayed tree from the last result set from the data source
	 */
	public DefaultMutableTreeNode getTree(String dataSource) throws Exception {

		if (lastResultSet != null) {
			dbsucxentW.constructW.TreeReconstructor reconstruct = new dbsucxentW.constructW.TreeReconstructor(
					lastResultSet, this.contextDb.getDataSource(dataSource), 0,
					0);
			DefaultMutableTreeNode returnNode = reconstruct.reconstructTree();
			if (reconstruct.isOverflow()) {
				throw (new OutOfMemoryException());
			}
			return returnNode;
		}

		return null;
	}

	/**
	 * Loads all information regarding the documents of the data source into
	 * memory
	 * 
	 * @param db
	 */
	@Override
	public void retrieveDocuments(DataSource dataSource) {
		try {
			String sql = "SELECT * FROM #DATASRC#_DocumentRValue";
			sql = sql.replace("#DATASRC#", dataSource.getName());
			Statement statement = dataSource.getDatabase().getDbConnection()
					.createStatement();
			ResultSet result = statement.executeQuery(sql);

			dataSource.clearDocuments();
			int docId = 0, level;
			long rValue, ancestorValue;
			while (result.next()) {
				docId = result.getInt("DocId");
				level = result.getByte("Level");
				rValue = result.getLong("RValue");
				ancestorValue = result.getLong("AncestorValue");

				Document document = dataSource.tryGetDocument(docId);
				document.setValues(level, rValue, ancestorValue);
			}
		} catch (SQLException ex) {
			System.err.println("Exception when retrieving values");
		}
	}

	/**
	 * Checks whether a table with the given table name is in the database
	 * 
	 * @param db
	 *            The database
	 * @param tableName
	 *            The table name
	 * @return True if the table is in the database; otherwise, no
	 * @throws SQLException
	 */
	public static boolean isTableExisting(DatabaseInfo db, String tableName)
			throws SQLException {
		Statement statement = db.getDbConnection().createStatement();
		statement.executeUpdate("USE [" + db.getDbName() + "];");
		String sql = "SELECT name FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#TableName#]') AND type in (N'U')";
		sql = sql.replace("#TableName#", tableName);
		ResultSet result = statement.executeQuery(sql);
		boolean ret = result.next();
		result.close();
		statement.close();
		return ret;
	}

	/**
	 * Builds the string of pathId of a leaf path list (i.e. (pathId1, pathId2,
	 * ....)
	 * 
	 * @param leafPaths
	 *            List of leaf paths
	 * @return String of path Ids of those leaf paths
	 */
	public static String buildPathIdListString(Collection<ILeafPath> leafPaths) {
		StringBuilder idBuilder = new StringBuilder("(");
		boolean first = true;
		for (ILeafPath idPath : leafPaths) {
			if (first) {
				idBuilder.append(idPath.getPathId());
				first = false;
			} else
				idBuilder.append(", " + idPath.getPathId());
		}
		idBuilder.append(")");
		return idBuilder.toString();
	}
}
